import math
from typing import Any, List, Literal, Optional
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import not_, select, desc
from sqlalchemy import select, distinct, func, desc
from sqlalchemy.orm import joinedload
from sqlalchemy.ext.asyncio import AsyncSession
from app.models import Announcement
from app.models import AnnouncementCampus
from app.schemas import Api
from app.schemas.basic import Paginated
from app.schemas.announcement import AnnouncementDetail, AnnouncementFilter

class AnnouncementService:
    def __init__(self, db: AsyncSession):
        self.db = db
        pass

    async def get_announcement_filter(
        self, 
        page: int = 1, 
        size: int = 10,
        query: Optional[str] = None, 
        locations: Literal[None, 0, 1] = None, 
        categories: Optional[List[int]] = None, 
        sort_by: Literal[None, 0, 1] = None, 
    ) -> Api[Paginated[AnnouncementFilter]]:
        # 构建基础查询
        stmt = select(Announcement)
        # 过滤条件
        if query:
            stmt = stmt.filter(Announcement.name.like(f"%{query}%"))

        全部校区 = None
        卫津路 = 0
        北洋园 = 1
        if locations:
            # 使用关系表查询校区
            campus_stmt = select(AnnouncementCampus.announcement_id)
            if locations is not None:
                campus_stmt = campus_stmt.filter(AnnouncementCampus.campus == locations)
            campus_stmt = campus_stmt.distinct()
            stmt = stmt.filter(Announcement.id.in_(campus_stmt))

        # TODO: 暂时不支持分类查询
        # if categories:
        #     stmt = stmt.filter(Activity.type.in_(categories))

        stmt = stmt.options(joinedload(Announcement.publisher_relation))

        # 排序
        最新发布 = 0
        最近活动 = 1
        if sort_by == 最近活动:
            stmt = stmt.order_by(desc(Announcement.published_time))
        elif sort_by == 最新发布:
            stmt = stmt.order_by(desc(Announcement.published_time))
        else:  # 默认按 ID 排序
            stmt = stmt.order_by(desc(Announcement.id))
        
        # 计算总数和分页
        total_stmt = select(func.count()).select_from(stmt.subquery())
        total = await self.db.execute(total_stmt)
        total_pages = math.ceil(total.scalar_one() / size)

        # 分页查询
        stmt = stmt.offset((page - 1) * size).limit(size)
        activities = await self.db.execute(stmt)

        result = []
        for activity in activities.scalars():
            obj = AnnouncementFilter.model_validate({
                "id": activity.id,
                "name": activity.name,
                "date_time": activity.published_time,
                "type_id": activity.type,
                "poster": activity.poster,
                "publisher_name": activity.publisher_relation.display_name,
                "publisher_avatar": activity.publisher_relation.avatar,
            })
            result.append(obj)
        obj = Paginated[AnnouncementFilter].model_validate({
            "total_page": total_pages,
            "current_page": page,
            "has_next": page < total_pages,
            "data": result,
        })
        obj = Api[Paginated[AnnouncementFilter]].model_validate({
            "code": 10000,
            "data": obj,
        })
        return obj
    
    async def get_announcement_detail(self, id: int) -> Api[AnnouncementDetail]:
        stmt = select(Announcement).filter(Announcement.id == id).options(joinedload(Announcement.publisher_relation))
        announcement = await self.db.execute(stmt)
        announcement = announcement.scalar_one_or_none()
        # if not announcement:
        #     obj = Api[AnnouncementDetail].model_validate({
        #         "code": 40001,
        #         "data": "请求数据不存在",
        #     })
        #     return obj

        obj = AnnouncementDetail.model_validate({
            "id": announcement.id,
            "name": announcement.name,
            "published_time": announcement.published_time,
            "type": announcement.type,
            "poster": announcement.poster,
            "publisher_name": announcement.publisher_relation.display_name,
            "publisher_avatar": announcement.publisher_relation.avatar,
        })
        obj = Api[AnnouncementDetail].model_validate({
            "code": 10000,
            "data": obj,
        })
        return obj

    
    async def get_announcement_content(self, id: int) -> Api[str]:
        stmt = select(Announcement).filter(Announcement.id == id)
        announcement = await self.db.execute(stmt)
        announcement = announcement.scalar_one_or_none()
        # if not announcement:
        #     obj = Api[str].model_validate({

        obj = Api[str].model_validate({
            "code": 10000,
            "data": announcement.content,
        })
        return obj
    
    async def get_announcement_poster(self, id: int) -> Api[str]:
        stmt = select(Announcement).filter(Announcement.id == id)
        announcement = await self.db.execute(stmt)
        announcement = announcement.scalar_one_or_none()
        # if not announcement:
        #     obj = Api[str].model_validate({
        #         "code": 40001,
        #         "data": "请求数据不存在",
        #     })
        #     return obj

        obj = Api[str].model_validate({
            "code": 10000,
            "data": announcement.poster,
        })
        return obj